/******************************************************************************
 * @File name   :      TicketPriceOfDealerDaoImpl.java
 *
 * @Author      :      GUDONG
 *
 * @Date        :      2015年11月3日
 *
 * @Copyright Notice: 
 * Copyright (c) 2014 Capgemini, Inc. All  Rights Reserved.
 * This software is published under the terms of the Capgemini Software
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file.
 * 
 * 
 * ----------------------------------------------------------------------------
 * Date                   Who         Version        Comments
 * 2015年11月3日 下午9:16:38        GUDONG     1.0            Initial Version
 *****************************************************************************/
package com.cap.bts.crmdp.dealer.coupon.dao.impl;

import java.math.BigInteger;
import java.util.List;

import javax.inject.Inject;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.coupon.constants.CouponConstants;
import com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao;
import com.cap.bts.crmdp.dealer.coupon.domain.TicketPriceOfDealer;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;

/**
 *
 */
@Repository("ITicketPriceOfDealerDao")
public class TicketPriceOfDealerDaoImpl extends BaseDaoImpl<TicketPriceOfDealer, Long> implements ITicketPriceOfDealerDao {

    /**
     * @Date        :      2015年11月3日
     * @param clazz
     */
    @Inject
    public TicketPriceOfDealerDaoImpl(@Value("com.cap.bts.crmdp.dealer.coupon.domain.TicketPriceOfDealer") Class<TicketPriceOfDealer> clazz) {
        super(clazz);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月4日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#deleteTicketLinePriceByTicketLineId(java.lang.Long)
    **/
    @Override
    public void deleteTicketLinePriceByTicketLineId(Long ticketLineId) throws AppException {
        String sql = "delete from tm_raw_ticket_line_price where ticket_line_id = " + ticketLineId;
        this.getEntityManager().createNativeQuery(sql).executeUpdate();
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月4日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#checkTicketLinePriceByTicketLineId(java.lang.Long)
    **/
    @Override
    public List<TicketPriceOfDealer> checkTicketLinePriceByTicketLineId(Long ticketLineId)
        throws AppException {
        String sql = "select * from tm_raw_ticket_line_price a where a.ticket_line_id = '" + ticketLineId + "'";
        return this.getListByNativeQuery(sql);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月4日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#checkTicketLinePriceNotInPackagePriceByTicketLineId()
    **/
    @Override
    public List<BigInteger> checkTicketLinePriceNotInPackagePriceByTicketLineId()
        throws AppException {
        String sql = "select DISTINCT a.ticket_line_id from tm_raw_ticket_line_price a where"
            + " not exists"
            + " (select * from tm_raw_package_price b where a.ticket_line_id = b.ticket_line_id)";
        return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<BigInteger>(sql));
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月5日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#getTicketLinePriceByTicketLineIdAndDealerId(java.lang.Long, java.lang.Long)
    **/
    @Override
    public List<TicketPriceOfDealer> getTicketLinePriceByTicketLineIdAndDealerId(Long ticketLineId,
        Long dealerId) throws AppException {
        String sql = "select * from tm_raw_ticket_line_price a"
            + " where a.ticket_line_id = " + ticketLineId
            + " and a.dealer_id = " + dealerId;
        return this.getListByNativeQuery(sql);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月9日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#getDealerIdByTicketLineId()
    **/
    @Override
    public List<BigInteger> getDealerId() throws AppException {
        String sql = "select distinct a.dealer_id from tm_raw_ticket_line_price a";
        return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<BigInteger>(sql));
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月9日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#getTicketDefIdAndDealerId()
    **/
    @Override
    public List<Object[]> getTicketDefIdAndDealerId() throws AppException {
        String sql = "SELECT b.TICKET_DEF_ID, a.dealer_id"
            + " FROM tm_raw_ticket_line_price a, tm_raw_ticket_line_def b"
            + " WHERE a.object_type = 'ticketLine' AND a.ticket_line_id = b.ID"
            + "  GROUP BY b.TICKET_DEF_ID, a.dealer_id";
        return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月9日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#getPackageDefIdAndDealerId()
    **/
    @Override
    public List<Object[]> getPackageDefIdAndDealerId() throws AppException {
        String sql = "SELECT b.PACKAGE_DEF_ID, c.dealer_id"
            + " FROM "
            + "(SELECT b.TICKET_DEF_ID, a.dealer_id"
            + " FROM tm_raw_ticket_line_price a, tm_raw_ticket_line_def b"
            + " WHERE a.object_type = 'ticketLine' AND a.ticket_line_id = b.ID"
            + " GROUP BY b.TICKET_DEF_ID, a.dealer_id) c,"
            + " tm_raw_package_redemption_lines b,"
            + " tm_raw_ticket_line_price"
            + " WHERE tm_raw_ticket_line_price.object_type = 'ticket' AND c.TICKET_DEF_ID = b.TICKET_DEF_ID"
            + " group by b.PACKAGE_DEF_ID, c.dealer_id";
        return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月10日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#deletePackageAndTicketLine()
    **/
    @Override
    public void deletePackageAndTicketLine() throws AppException {
        String sql = "DELETE FROM tm_raw_ticket_line_price"
            + " WHERE object_type in('"+CouponConstants.PACKAGE+"','"+CouponConstants.TICKET+"')";
        this.getEntityManager().createNativeQuery(sql).executeUpdate();
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月19日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#getTicketPriceOfDealerByDealerAndPackageDefId(java.lang.Long, java.lang.Long)
    **/
    @Override
    public List<TicketPriceOfDealer> getTicketPriceOfDealerByDealerAndPackageDefId(Long dealerId,
        Long packageDefId) throws AppException {
        String sql = "select * from tm_raw_ticket_line_price a"
            + " where a.dealer_id = " + dealerId
            + " and a.object_id = " + packageDefId
            + " and a.object_type = '" + CouponConstants.PACKAGE + "'";
        return this.getListByNativeQuery(sql);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月19日
     * @see com.cap.bts.crmdp.dealer.coupon.dao.ITicketPriceOfDealerDao#getTicketPriceOfDealerByDealerIdAndTicketLineIdAndObjType(java.lang.Long, java.lang.Long, java.lang.String)
    **/
    @Override
    public List<TicketPriceOfDealer> getTicketPriceOfDealerByDealerIdAndTicketLineIdAndObjType(
        Long dealerId, Long ticketLineId, String objectType) throws AppException {
        String sql = "select * from tm_raw_ticket_line_price a "
            + " where a.dealer_id = " + dealerId
            + " and a.object_id = " + ticketLineId
            + " and a.object_type = '" + objectType + "'";
        return this.getListByNativeQuery(sql);
    }

}
